package com.embraces.service.mpi;

import com.embraces.constant.EsConstants;
import com.embraces.domain.user.UOrg;
import com.embraces.repository.mpi.CheckTimelineRepository;
import com.embraces.repository.mpi.StationManageRepository;
import com.embraces.repository.mpi.WarningInformationRepository;
import com.embraces.repository.user.UOrgRepository;
import com.embraces.web.dto.mpi.*;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import javax.transaction.Transactional;
import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Echarts统计数据Service
 */
@Service
@Transactional(rollbackOn = Exception.class)
public class EchartsDataService {

    private static final String WEEK = "week";
    private static final String MONTH = "month";
    private static final String HALFYEAR = "halfYear";

    @Resource
    private WarningInformationRepository warningInformationRepository;
    @Resource
    private CheckTimelineRepository checkTimelineRepository;
    @Resource
    private UOrgRepository uOrgRepository;
    @Resource
    private StationManageRepository stationManageRepository;

    /**
     * 内部方法，获取当前天以及当前天7天前的日期
     *
     * @return
     */
    public static String[] timeScope(String timeScope) {
        Date date = new Date();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Calendar calendar = Calendar.getInstance();
        String[] timeScpoes = new String[2];
        if (WEEK.equals(timeScope)) {
            String today = simpleDateFormat.format(date);
            calendar.set(Calendar.DAY_OF_YEAR, calendar.get(Calendar.DAY_OF_YEAR) - 6);
            Date earlyDay = calendar.getTime();
            String earlySevenDay = simpleDateFormat.format(earlyDay);
            timeScpoes[0] = today;
            timeScpoes[1] = earlySevenDay;
        } else if (MONTH.equals(timeScope)) {
            String today = simpleDateFormat.format(date);
            calendar.set(Calendar.DAY_OF_YEAR, calendar.get(Calendar.DAY_OF_YEAR) - 30);
            Date earlyDay = calendar.getTime();
            String earlyThirtyOneDay = simpleDateFormat.format(earlyDay);
            timeScpoes[0] = today;
            timeScpoes[1] = earlyThirtyOneDay;
        } else if (HALFYEAR.equals(timeScope)) {
            int todayMonth = calendar.get(Calendar.MONTH) + 1;
            String todayMonthTemp = todayMonth > 9 ? "" + todayMonth : "0" + todayMonth;
            int todayYear = calendar.get(Calendar.YEAR);
            calendar.add(Calendar.MONTH, -5);
            int earlyMonth = calendar.get(Calendar.MONTH) + 1;
            String earlyMonthTemp = earlyMonth > 9 ? "" + earlyMonth : "0" + earlyMonth;
            int earlyYear = calendar.get(Calendar.YEAR);
            String today = todayYear + "-" + todayMonthTemp;
            String early = earlyYear + "-" + earlyMonthTemp;
            timeScpoes[0] = today;
            timeScpoes[1] = early;
        } else {
            int todayMonth = calendar.get(Calendar.MONTH) + 1;
            String todayMonthTemp = todayMonth > 9 ? "" + todayMonth : "0" + todayMonth;
            int todayYear = calendar.get(Calendar.YEAR);
            calendar.add(Calendar.MONTH, -11);
            int earlyMonth = calendar.get(Calendar.MONTH) + 1;
            String earlyMonthTemp = earlyMonth > 9 ? "" + earlyMonth : "0" + earlyMonth;
            int earlyYear = calendar.get(Calendar.YEAR);
            String today = todayYear + "-" + todayMonthTemp;
            String early = earlyYear + "-" + earlyMonthTemp;
            timeScpoes[0] = today;
            timeScpoes[1] = early;
        }
        return timeScpoes;
    }

    /**
     * 核查综合统计Echarts数据
     *
     * @param timeScope 时间范围（week-本周,month-本月,halfYear-半年,totalYear-全年）
     * @return
     */
    public List<CheckComprehensiveCountDTO> checkComprehensiveCount(String trainDate, String endDate, List<String> deptNames, Long uOrgId, String timeScope) {
        Map<String, Object> params = new HashMap<>();
        String[] timeScopes = timeScope(timeScope);
        List<CheckComprehensiveCountDTO> checkComprehensiveCountDTOS;
        //时间不为空，下拉框为空
        if (!StringUtils.isEmpty(trainDate) && !StringUtils.isEmpty(endDate) && StringUtils.isEmpty(deptNames)) {
            ArrayList<Long> uOrgIds = EsConstants.powerList.get(uOrgId);
            params.put("uOrgIds", uOrgIds);
            String sql = sqlCheckComprehensiveCount(trainDate, endDate);
            checkComprehensiveCountDTOS = warningInformationRepository.findListBySql(CheckComprehensiveCountDTO.class, 0, sql, params);
            //时间不为空，下拉框不为空
        } else if (!StringUtils.isEmpty(trainDate) && !StringUtils.isEmpty(endDate) && !StringUtils.isEmpty(deptNames)) {
            String s = deptNames.get(0);
            List<BigInteger> orgIds = uOrgRepository.findOrgIdByName(deptNames);
            List<Long> uOrgIds = new ArrayList<>();
            for (BigInteger orgId : orgIds
                    ) {
                uOrgIds.addAll(EsConstants.powerList.get(Long.valueOf(orgId.toString())));
            }
            params.put("uOrgIds", uOrgIds);
            String sql = sqlCheckComprehensiveCount(trainDate, endDate);
            checkComprehensiveCountDTOS = warningInformationRepository.findListBySql(CheckComprehensiveCountDTO.class, 0, sql, params);
        } else {
            //下拉框为空
            if (StringUtils.isEmpty(deptNames)) {
                ArrayList<Long> uOrgIds = EsConstants.powerList.get(uOrgId);
                params.put("uOrgIds", uOrgIds);
                if (WEEK.equals(timeScope) || MONTH.equals(timeScope)) {
                    String sql = sqlCheckComprehensiveCount(timeScopes[1], timeScopes[0]);
                    checkComprehensiveCountDTOS = warningInformationRepository.findListBySql(CheckComprehensiveCountDTO.class, 0, sql, params);
                } else {
                    String sqlYear = sqlCheckComprehensiveCountYear(timeScopes[1], timeScopes[0]);
                    checkComprehensiveCountDTOS = warningInformationRepository.findListBySql(CheckComprehensiveCountDTO.class, 0, sqlYear, params);
                }
            } else {
                List<BigInteger> orgIds = uOrgRepository.findOrgIdByName(deptNames);
                List<Long> uOrgIds = new ArrayList<>();
                for (BigInteger orgId : orgIds
                        ) {
                    uOrgIds.addAll(EsConstants.powerList.get(Long.valueOf(orgId.toString())));
                }
                params.put("uOrgIds", uOrgIds);
                if (WEEK.equals(timeScope) || MONTH.equals(timeScope)) {
                    String sql = sqlCheckComprehensiveCount(timeScopes[1], timeScopes[0]);
                    checkComprehensiveCountDTOS = warningInformationRepository.findListBySql(CheckComprehensiveCountDTO.class, 0, sql, params);
                } else {
                    String sqlYear = sqlCheckComprehensiveCountYear(timeScopes[1], timeScopes[0]);
                    checkComprehensiveCountDTOS = warningInformationRepository.findListBySql(CheckComprehensiveCountDTO.class, 0, sqlYear, params);
                }
            }
        }
        return checkComprehensiveCountDTOS;
    }

    /**
     * 核查综合统计Echarts数据 sql 查询语句（本周，本月）
     *
     * @param startTime
     * @param endTime
     * @return
     */
    private String sqlCheckComprehensiveCount(String startTime, String endTime) {
        String sql = "SELECT " +
                " timeScope.selectTime AS xAsix, " +
                " COALESCE ( warnNum.warnCount, 0 ) AS warnNums, " +
                " COALESCE ( checkNum.checkCount, 0 ) AS checkNums  " +
                "FROM " +
                " ( " +
                "SELECT " +
                " to_char( DATE ( zz ), 'yyyy-mm-dd' ) AS selectTime  " +
                "FROM " +
                " generate_series ( date_trunc( 'day', to_date( '" + startTime + "', 'yyyy-mm-dd' )), date_trunc( 'day', to_date( '" + endTime + "', 'yyyy-mm-dd' )), '1 day' ) AS tt ( zz )) AS timeScope " +
                " LEFT JOIN ( SELECT COUNT ( passenger_id_no ) AS warnCount, SUBSTRING(create_time FROM 1 FOR 10) as create_time FROM mpi_warning_information WHERE ID IN ( SELECT warn_info_id FROM mpi_warn_info_user WHERE u_org_id in :uOrgIds )  AND ticket_state = '1' AND area != 1  GROUP BY SUBSTRING(create_time FROM 1 FOR 10) ) AS warnNum ON warnNum.create_time = timeScope.selectTime " +
                " LEFT JOIN ( " +
                "SELECT COUNT " +
                " ( passenger_id_no ) AS checkCount, " +
                " SUBSTRING(create_time FROM 1 FOR 10) as create_time " +
                "FROM " +
                " mpi_warning_information  " +
                "WHERE " +
                " ID IN ( SELECT warn_info_id FROM mpi_warn_info_user WHERE u_org_id in :uOrgIds )  " +
                " AND check_state = '核查结束'  " +
                "GROUP BY " +
                " SUBSTRING(create_time FROM 1 FOR 10)  " +
                " ) AS checkNum ON checkNum.create_time = timeScope.selectTime";
        return sql;
    }

    /**
     * 核查综合统计Echarts数据 sql 查询语句（半年，一年）
     *
     * @param startTime
     * @param endTime
     * @return
     */
    private String sqlCheckComprehensiveCountYear(String startTime, String endTime) {
        String sql = "SELECT" +
                " timeScope.selectTime AS xAsix," +
                " COALESCE ( warnNum.warnCount, 0 ) AS warnNums," +
                " COALESCE ( checkNum.checkCount, 0 ) AS checkNums " +
                "FROM" +
                " (" +
                " SELECT" +
                " to_char( DATE ( zz ), 'yyyy-mm' ) AS selectTime " +
                " FROM" +
                " generate_series ( date_trunc( 'month', to_date( '" + startTime + "', 'yyyy-mm' )), date_trunc( 'month', to_date( '" + endTime + "', 'yyyy-mm' )), '1 month' ) AS tt ( zz )) AS timeScope" +
                " LEFT JOIN (" +
                " SELECT" +
                " newDate.yearDate AS newTime," +
                " COUNT (*) AS warnCount " +
                " FROM" +
                " (" +
                " SELECT SUBSTRING" +
                " ( create_time FROM 1 FOR 7 ) AS yearDate," +
                " passenger_id_no AS idcard " +
                " FROM" +
                " mpi_warning_information " +
                " WHERE" +
                " ID IN ( SELECT warn_info_id FROM mpi_warn_info_user WHERE u_org_id in :uOrgIds ) AND ticket_state = '1' AND area != 1 ) AS newDate " +
                " GROUP BY" +
                " newTime " +
                " ) AS warnNum ON warnNum.newTime = timeScope.selectTime" +
                " LEFT JOIN (" +
                " SELECT" +
                " newDate.yearDate AS newTime," +
                " COUNT (*) AS checkCount " +
                " FROM" +
                " (" +
                " SELECT SUBSTRING" +
                " ( create_time FROM 1 FOR 7 ) AS yearDate," +
                " passenger_id_no AS idcard " +
                " FROM" +
                " mpi_warning_information " +
                " WHERE" +
                " ID IN ( SELECT warn_info_id FROM mpi_warn_info_user WHERE u_org_id in :uOrgIds) AND check_state = '核查结束' AND ticket_state = '1' AND area != 1) AS newDate " +
                " GROUP BY" +
                " newTime " +
                " ) AS checkNum ON checkNum.newTime = timeScope.selectTime";
        return sql;
    }

    /**
     * 核查人员处理结果统计Echarts数据
     *
     * @param timeScope
     * @param uOrgId
     * @return
     */
    public List<InspectorsProcessResultCountDTO> inspectorsProcessResultCount(String trainDate, String endDate, List<String> deptNames, Long uOrgId, String timeScope) {
        Map<String, Object> params = new HashMap<>();
        String[] timeScopes = timeScope(timeScope);
        List<InspectorsProcessResultCountDTO> inspectorsProcessResultCountDTOS;
        if (!StringUtils.isEmpty(trainDate) && !StringUtils.isEmpty(endDate) && StringUtils.isEmpty(deptNames)) {
            ArrayList<Long> uOrgIds = EsConstants.powerList.get(uOrgId);
            params.put("uOrgIds", uOrgIds);
            String sql = sqlInspectorsProcessResultCount(trainDate, endDate);
            inspectorsProcessResultCountDTOS = warningInformationRepository.findListBySql(InspectorsProcessResultCountDTO.class, 0, sql, params);
        } else if (!StringUtils.isEmpty(trainDate) && !StringUtils.isEmpty(endDate) && !StringUtils.isEmpty(deptNames)) {
            List<BigInteger> orgIds = uOrgRepository.findOrgIdByName(deptNames);
            List<Long> uOrgIds = new ArrayList<>();
            for (BigInteger orgId : orgIds
                    ) {
                uOrgIds.addAll(EsConstants.powerList.get(Long.valueOf(orgId.toString())));
            }
            params.put("uOrgIds", uOrgIds);
            String sql = sqlInspectorsProcessResultCount(trainDate, endDate);
            inspectorsProcessResultCountDTOS = warningInformationRepository.findListBySql(InspectorsProcessResultCountDTO.class, 0, sql, params);
        } else {
            if (StringUtils.isEmpty(deptNames)) {
                ArrayList<Long> uOrgIds = EsConstants.powerList.get(uOrgId);
                params.put("uOrgIds", uOrgIds);
                if (WEEK.equals(timeScope) || MONTH.equals(timeScope)) {
                    String sql = sqlInspectorsProcessResultCount(timeScopes[1], timeScopes[0]);
                    inspectorsProcessResultCountDTOS = warningInformationRepository.findListBySql(InspectorsProcessResultCountDTO.class, 0, sql, params);
                } else {
                    String sqlYear = sqlInspectorsProcessResultCountYear(timeScopes[1], timeScopes[0]);
                    inspectorsProcessResultCountDTOS = warningInformationRepository.findListBySql(InspectorsProcessResultCountDTO.class, 0, sqlYear, params);
                }
            } else {
                List<BigInteger> orgIds = uOrgRepository.findOrgIdByName(deptNames);
                List<Long> uOrgIds = new ArrayList<>();
                for (BigInteger orgId : orgIds
                        ) {
                    uOrgIds.addAll(EsConstants.powerList.get(Long.valueOf(orgId.toString())));
                }
                params.put("uOrgIds", uOrgIds);
                if (WEEK.equals(timeScope) || MONTH.equals(timeScope)) {
                    String sql = sqlInspectorsProcessResultCount(timeScopes[1], timeScopes[0]);
                    inspectorsProcessResultCountDTOS = warningInformationRepository.findListBySql(InspectorsProcessResultCountDTO.class, 0, sql, params);
                } else {
                    String sqlYear = sqlInspectorsProcessResultCountYear(timeScopes[1], timeScopes[0]);
                    inspectorsProcessResultCountDTOS = warningInformationRepository.findListBySql(InspectorsProcessResultCountDTO.class, 0, sqlYear, params);
                }
            }
        }


        return inspectorsProcessResultCountDTOS;
    }

    /**
     * 核查人员处理结果统计Echarts数据 sql 查询语句（本周，本月）
     *
     * @param startTime
     * @param endTime
     * @return
     */
    private String sqlInspectorsProcessResultCount(String startTime, String endTime) {
        String sql = "SELECT" +
                " timeScope.selectTime AS xAsix," +
                " COALESCE ( releaseNum.releaseNums, 0 ) AS release," +
                " COALESCE ( dyingNum.dyingNums, 0 ) AS dying," +
                " COALESCE ( transferNum.transferNums, 0 ) AS transfer," +
                " COALESCE ( grabNum.grabNums, 0 ) AS grab " +
                "FROM" +
                " (" +
                " SELECT" +
                " to_char( DATE ( zz ), 'yyyy-mm-dd' ) AS selectTime " +
                " FROM" +
                " generate_series ( date_trunc( 'day', to_date( '" + startTime + "', 'yyyy-mm-dd' )), date_trunc( 'day', to_date( '" + endTime + "', 'yyyy-mm-dd' )), '1 day' ) AS tt ( zz )) AS timeScope" +
                " LEFT JOIN (" +
                " SELECT" +
                " newDate.yearDate AS newTime," +
                " COUNT (*) AS releaseNums " +
                " FROM" +
                " ((" +
                " SELECT SUBSTRING" +
                " ( check_time FROM 1 FOR 10 ) AS yearDate," +
                " passenger_id_no AS idcard " +
                " FROM" +
                " mpi_check_record " +
                " WHERE" +
                " ID IN ( SELECT check_id FROM mpi_check_timeline WHERE u_org_id in :uOrgIds AND warning_stage = '2' ) " +
                " AND check_result = '放行' " +
                " )) AS newDate " +
                " GROUP BY" +
                " newTime " +
                " ) AS releaseNum ON releaseNum.newTime = timeScope.selectTime" +
                " LEFT JOIN (" +
                " SELECT" +
                " newDate.yearDate AS newTime," +
                " COUNT (*) AS dyingNums " +
                " FROM" +
                " ((" +
                " SELECT SUBSTRING" +
                " ( check_time FROM 1 FOR 10 ) AS yearDate," +
                " passenger_id_no AS idcard " +
                " FROM" +
                " mpi_check_record " +
                " WHERE" +
                " ID IN ( SELECT check_id FROM mpi_check_timeline WHERE u_org_id in :uOrgIds AND warning_stage = '2' ) " +
                " AND check_result = '盯控' " +
                " )) AS newDate " +
                " GROUP BY" +
                " newTime " +
                " ) AS dyingNum ON dyingNum.newTime = timeScope.selectTime" +
                " LEFT JOIN (" +
                " SELECT" +
                " newDate.yearDate AS newTime," +
                " COUNT (*) AS transferNums " +
                " FROM" +
                " ((" +
                " SELECT SUBSTRING" +
                " ( check_time FROM 1 FOR 10 ) AS yearDate," +
                " passenger_id_no AS idcard " +
                " FROM" +
                " mpi_check_record " +
                " WHERE" +
                " ID IN ( SELECT check_id FROM mpi_check_timeline WHERE u_org_id in :uOrgIds AND warning_stage = '2' ) " +
                " AND check_result = '移交' " +
                " )) AS newDate " +
                " GROUP BY" +
                " newTime " +
                " ) AS transferNum ON transferNum.newTime = timeScope.selectTime" +
                " LEFT JOIN (" +
                " SELECT" +
                " newDate.yearDate AS newTime," +
                " COUNT (*) AS grabNums " +
                " FROM" +
                " ((" +
                " SELECT SUBSTRING" +
                " ( check_time FROM 1 FOR 10 ) AS yearDate," +
                " passenger_id_no AS idcard " +
                " FROM" +
                " mpi_check_record " +
                " WHERE" +
                " ID IN ( SELECT check_id FROM mpi_check_timeline WHERE u_org_id in :uOrgIds AND warning_stage = '2' ) " +
                " AND check_result = '抓捕' " +
                " )) AS newDate " +
                " GROUP BY" +
                " newTime " +
                " ) AS grabNum ON grabNum.newTime = timeScope.selectTime";
        return sql;
    }

    /**
     * 核查人员处理结果统计Echarts数据 sql 查询语句（半年，一年）
     *
     * @param startTime
     * @param endTime
     * @return
     */
    private String sqlInspectorsProcessResultCountYear(String startTime, String endTime) {
        String sql = "SELECT" +
                " timeScope.selectTime AS xAsix," +
                " COALESCE ( releaseNum.releaseNums, 0 ) AS RELEASE," +
                " COALESCE ( dyingNum.dyingNums, 0 ) AS dying," +
                " COALESCE ( transferNum.transferNums, 0 ) AS transfer," +
                " COALESCE ( grabNum.grabNums, 0 ) AS grab " +
                "FROM" +
                " (" +
                " SELECT" +
                " to_char( DATE ( zz ), 'yyyy-mm' ) AS selectTime " +
                " FROM" +
                " generate_series ( date_trunc( 'month', to_date( '" + startTime + "', 'yyyy-mm' )), date_trunc( 'month', to_date( '" + endTime + "', 'yyyy-mm' )), '1 month' ) AS tt ( zz )) AS timeScope" +
                " LEFT JOIN (" +
                " SELECT" +
                " newDate.yearDate AS newTime," +
                " COUNT (*) AS releaseNums " +
                " FROM" +
                " ((" +
                " SELECT SUBSTRING" +
                " ( check_time FROM 1 FOR 7 ) AS yearDate," +
                " passenger_id_no AS idcard " +
                " FROM" +
                " mpi_check_record " +
                " WHERE" +
                " ID IN ( SELECT check_id FROM mpi_check_timeline WHERE u_org_id in :uOrgIds AND warning_stage = '2' ) " +
                " AND check_result = '放行' " +
                " )) AS newDate " +
                " GROUP BY" +
                " newTime " +
                " ) AS releaseNum ON releaseNum.newTime = timeScope.selectTime" +
                " LEFT JOIN (" +
                " SELECT" +
                " newDate.yearDate AS newTime," +
                " COUNT (*) AS dyingNums " +
                " FROM" +
                " ((" +
                " SELECT SUBSTRING" +
                " ( check_time FROM 1 FOR 7 ) AS yearDate," +
                " passenger_id_no AS idcard " +
                " FROM" +
                " mpi_check_record " +
                " WHERE" +
                " ID IN ( SELECT check_id FROM mpi_check_timeline WHERE u_org_id in :uOrgIds AND warning_stage = '2' ) " +
                " AND check_result = '盯控' " +
                " )) AS newDate " +
                " GROUP BY" +
                " newTime " +
                " ) AS dyingNum ON dyingNum.newTime = timeScope.selectTime" +
                " LEFT JOIN (" +
                " SELECT" +
                " newDate.yearDate AS newTime," +
                " COUNT (*) AS transferNums " +
                " FROM" +
                " ((" +
                " SELECT SUBSTRING" +
                " ( check_time FROM 1 FOR 7 ) AS yearDate," +
                " passenger_id_no AS idcard " +
                " FROM" +
                " mpi_check_record " +
                " WHERE" +
                " ID IN ( SELECT check_id FROM mpi_check_timeline WHERE u_org_id in :uOrgIds AND warning_stage = '2' ) " +
                " AND check_result = '移交' " +
                " )) AS newDate " +
                " GROUP BY" +
                " newTime " +
                " ) AS transferNum ON transferNum.newTime = timeScope.selectTime" +
                " LEFT JOIN (" +
                " SELECT" +
                " newDate.yearDate AS newTime," +
                " COUNT (*) AS grabNums " +
                " FROM" +
                " ((" +
                " SELECT SUBSTRING" +
                " ( check_time FROM 1 FOR 7 ) AS yearDate," +
                " passenger_id_no AS idcard " +
                " FROM" +
                " mpi_check_record " +
                " WHERE" +
                " ID IN ( SELECT check_id FROM mpi_check_timeline WHERE u_org_id in :uOrgIds AND warning_stage = '2' ) " +
                " AND check_result = '抓捕' " +
                " )) AS newDate " +
                " GROUP BY" +
                " newTime " +
                " ) AS grabNum ON grabNum.newTime = timeScope.selectTime";
        return sql;
    }

    /**
     * 重点人员乘坐车次统计Echarts数据
     *
     * @param uOrgId
     * @param timeScope
     * @return
     */
    public List<KeyPersonnelRidesCountDTO> keyPersonnelRidesCount(String trainDate, String endDateH, List<String> deptNames, Long uOrgId, String timeScope) {
        Map<String, Object> params = new HashMap<>();
        String[] timeScopes = timeScope(timeScope);
        List<KeyPersonnelRidesCountDTO> keyPersonnelRidesCountDTOS;
        if (!StringUtils.isEmpty(trainDate) && !StringUtils.isEmpty(endDateH) && StringUtils.isEmpty(deptNames)) {
            ArrayList<Long> uOrgIds = EsConstants.powerList.get(uOrgId);
            params.put("uOrgIds", uOrgIds);
            String[] trainDateSplit = trainDate.split("-");
            String[] arrivalDateSplit = endDateH.split("-");
            String beginDate = trainDateSplit[0] + trainDateSplit[1] + trainDateSplit[2];
            String endDate = arrivalDateSplit[0] + arrivalDateSplit[1] + arrivalDateSplit[2];
            String sql = sqlKeyPersonnelRidesCount(beginDate, endDate);
            keyPersonnelRidesCountDTOS = warningInformationRepository.findListBySql(KeyPersonnelRidesCountDTO.class, 0, sql, params);
        } else if (!StringUtils.isEmpty(trainDate) && !StringUtils.isEmpty(endDateH) && !StringUtils.isEmpty(deptNames)) {
            List<BigInteger> orgIds = uOrgRepository.findOrgIdByName(deptNames);
            List<Long> uOrgIds = new ArrayList<>();
            for (BigInteger orgId : orgIds
                    ) {
                uOrgIds.addAll(EsConstants.powerList.get(Long.valueOf(orgId.toString())));
            }
            params.put("uOrgIds", uOrgIds);
            String[] trainDateSplit = trainDate.split("-");
            String[] arrivalDateSplit = endDateH.split("-");
            String beginDate = trainDateSplit[0] + trainDateSplit[1] + trainDateSplit[2];
            String endDate = arrivalDateSplit[0] + arrivalDateSplit[1] + arrivalDateSplit[2];
            String sql = sqlKeyPersonnelRidesCount(beginDate, endDate);
            keyPersonnelRidesCountDTOS = warningInformationRepository.findListBySql(KeyPersonnelRidesCountDTO.class, 0, sql, params);
        } else {
            if (StringUtils.isEmpty(deptNames)) {
                ArrayList<Long> uOrgIds = EsConstants.powerList.get(uOrgId);
                params.put("uOrgIds", uOrgIds);
                if (WEEK.equals(timeScope) || MONTH.equals(timeScope)) {
                    String[] startTimeSplit = timeScopes[1].split("-");
                    String[] endTimeSplit = timeScopes[0].split("-");
                    String bgTime = startTimeSplit[0] + startTimeSplit[1] + startTimeSplit[2];
                    String endTime = endTimeSplit[0] + endTimeSplit[1] + endTimeSplit[2];
                    String sql = sqlKeyPersonnelRidesCount(bgTime, endTime);
                    keyPersonnelRidesCountDTOS = warningInformationRepository.findListBySql(KeyPersonnelRidesCountDTO.class, 0, sql, params);
                } else {
                    String[] startTimeSplit = timeScopes[1].split("-");
                    String[] endTimeSplit = timeScopes[0].split("-");
                    String bgTime = startTimeSplit[0] + startTimeSplit[1];
                    String endTime = endTimeSplit[0] + endTimeSplit[1];
                    String sqlYear = sqlKeyPersonnelRidesCountYear(bgTime, endTime);
                    keyPersonnelRidesCountDTOS = warningInformationRepository.findListBySql(KeyPersonnelRidesCountDTO.class, 0, sqlYear, params);
                }
            } else {
                List<BigInteger> orgIds = uOrgRepository.findOrgIdByName(deptNames);
                List<Long> uOrgIds = new ArrayList<>();
                for (BigInteger orgId : orgIds
                        ) {
                    uOrgIds.addAll(EsConstants.powerList.get(Long.valueOf(orgId.toString())));
                }
                params.put("uOrgIds", uOrgIds);
                if (WEEK.equals(timeScope) || MONTH.equals(timeScope)) {
                    String[] startTimeSplit = timeScopes[1].split("-");
                    String[] endTimeSplit = timeScopes[0].split("-");
                    String bgTime = startTimeSplit[0] + startTimeSplit[1] + startTimeSplit[2];
                    String endTime = endTimeSplit[0] + endTimeSplit[1] + endTimeSplit[2];
                    String sql = sqlKeyPersonnelRidesCount(bgTime, endTime);
                    keyPersonnelRidesCountDTOS = warningInformationRepository.findListBySql(KeyPersonnelRidesCountDTO.class, 0, sql, params);
                } else {
                    String[] startTimeSplit = timeScopes[1].split("-");
                    String[] endTimeSplit = timeScopes[0].split("-");
                    String bgTime = startTimeSplit[0] + startTimeSplit[1];
                    String endTime = endTimeSplit[0] + endTimeSplit[1];
                    String sqlYear = sqlKeyPersonnelRidesCountYear(bgTime, endTime);
                    keyPersonnelRidesCountDTOS = warningInformationRepository.findListBySql(KeyPersonnelRidesCountDTO.class, 0, sqlYear, params);
                }
            }
        }
        return keyPersonnelRidesCountDTOS;
    }

    /**
     * 重点人员乘坐车次统计Echarts数据 sql 查询语句（本周，本月）
     *
     * @param startTime
     * @param endTime
     * @return
     */
    private String sqlKeyPersonnelRidesCount(String startTime, String endTime) {
        String sql = "SELECT" +
                " w1.train_no as carTrips," +
                " COUNT ( w1.* ) as carNums " +
                "FROM" +
                " mpi_warning_information as w1," +
                " ( SELECT train_date AS yeardate,id as formId FROM mpi_warning_information ) AS newdate " +
                "WHERE" +
                " ID IN ( SELECT warn_info_id FROM mpi_warn_info_user WHERE u_org_id in :uOrgIds ) " +
                " and w1.id = newdate.formId" +
                " AND newdate.yeardate >= '" + startTime + "' " +
                " AND newdate.yeardate <= '" + endTime + "' " +
                " AND w1.ticket_state = '1'  " +
                " AND w1.area != 1  " +
                "GROUP BY" +
                " (" +
                " w1.train_no)" +
                " ORDER BY carNums desc limit 10";
        return sql;
    }

    /**
     * 重点人员乘坐车次统计Echarts数据 sql 查询语句（半年，一年）
     *
     * @param startTime
     * @param endTime
     * @return
     */
    private String sqlKeyPersonnelRidesCountYear(String startTime, String endTime) {
        String sql = "SELECT" +
                " w1.train_no as carTrips," +
                " COUNT ( w1.* ) as carNums " +
                "FROM" +
                " mpi_warning_information as w1," +
                " ( SELECT SUBSTRING ( train_date FROM 1 FOR 6 ) AS yeardate,id as formId FROM mpi_warning_information ) AS newdate " +
                "WHERE" +
                " ID IN ( SELECT warn_info_id FROM mpi_warn_info_user WHERE u_org_id in :uOrgIds ) " +
                " and w1.id = newdate.formId" +
                " AND newdate.yeardate >= '" + startTime + "' " +
                " AND newdate.yeardate <= '" + endTime + "' " +
                " AND w1.ticket_state = '1'  " +
                " AND w1.area != 1  " +
                "GROUP BY" +
                " (" +
                " w1.train_no)" +
                " ORDER BY carNums desc limit 10";
        return sql;
    }

    /**
     * 重点人员到站统计Echarts数据
     *
     * @param timeScope
     * @param uOrgId
     * @return
     */
    public List<KeyPersonnelToStationCountDTO> keyPersonnelToStationCount(String trainDate, String endDateH, List<String> deptNames, Long uOrgId, String timeScope) {
        Map<String, Object> params = new HashMap<>();
        String[] timeScopes = timeScope(timeScope);
        List<KeyPersonnelToStationCountDTO> keyPersonnelToStationCountDTOS;

        if (!StringUtils.isEmpty(trainDate) && !StringUtils.isEmpty(endDateH) && StringUtils.isEmpty(deptNames)) {
            ArrayList<Long> uOrgIds = EsConstants.powerList.get(uOrgId);
            params.put("uOrgIds", uOrgIds);
            String[] trainDateSplit = trainDate.split("-");
            String[] arrivalDateSplit = endDateH.split("-");
            String beginDate = trainDateSplit[0] + trainDateSplit[1] + trainDateSplit[2];
            String endDate = arrivalDateSplit[0] + arrivalDateSplit[1] + arrivalDateSplit[2];
            String sql = sqlKeyPersonnelToStationCount(beginDate, endDate);
            keyPersonnelToStationCountDTOS = warningInformationRepository.findListBySql(KeyPersonnelToStationCountDTO.class, 0, sql, params);
        } else if (!StringUtils.isEmpty(trainDate) && !StringUtils.isEmpty(endDateH) && !StringUtils.isEmpty(deptNames)) {
            List<BigInteger> orgIds = uOrgRepository.findOrgIdByName(deptNames);
            List<Long> uOrgIds = new ArrayList<>();
            for (BigInteger orgId : orgIds
                    ) {
                uOrgIds.addAll(EsConstants.powerList.get(Long.valueOf(orgId.toString())));
            }
            params.put("uOrgIds", uOrgIds);
            String[] trainDateSplit = trainDate.split("-");
            String[] arrivalDateSplit = endDateH.split("-");
            String beginDate = trainDateSplit[0] + trainDateSplit[1] + trainDateSplit[2];
            String endDate = arrivalDateSplit[0] + arrivalDateSplit[1] + arrivalDateSplit[2];
            String sql = sqlKeyPersonnelToStationCount(beginDate, endDate);
            keyPersonnelToStationCountDTOS = warningInformationRepository.findListBySql(KeyPersonnelToStationCountDTO.class, 0, sql, params);

        } else {
            if (StringUtils.isEmpty(deptNames)) {

                ArrayList<Long> uOrgIds = EsConstants.powerList.get(uOrgId);
                params.put("uOrgIds", uOrgIds);
                if (WEEK.equals(timeScope) || MONTH.equals(timeScope)) {
                    String[] startTimeSplit = timeScopes[1].split("-");
                    String[] endTimeSplit = timeScopes[0].split("-");
                    String bgTime = startTimeSplit[0] + startTimeSplit[1] + startTimeSplit[2];
                    String endTime = endTimeSplit[0] + endTimeSplit[1] + endTimeSplit[2];
                    String sql = sqlKeyPersonnelToStationCount(bgTime, endTime);
                    keyPersonnelToStationCountDTOS = warningInformationRepository.findListBySql(KeyPersonnelToStationCountDTO.class, 0, sql, params);
                } else {
                    String[] startTimeSplit = timeScopes[1].split("-");
                    String[] endTimeSplit = timeScopes[0].split("-");
                    String bgTime = startTimeSplit[0] + startTimeSplit[1];
                    String endTime = endTimeSplit[0] + endTimeSplit[1];
                    String sqlYear = sqlKeyPersonnelToStationCountYear(bgTime, endTime);
                    keyPersonnelToStationCountDTOS = warningInformationRepository.findListBySql(KeyPersonnelToStationCountDTO.class, 0, sqlYear, params);
                }
            } else {
                List<BigInteger> orgIds = uOrgRepository.findOrgIdByName(deptNames);
                List<Long> uOrgIds = new ArrayList<>();
                for (BigInteger orgId : orgIds
                        ) {
                    uOrgIds.addAll(EsConstants.powerList.get(Long.valueOf(orgId.toString())));
                }
                params.put("uOrgIds", uOrgIds);
                if (WEEK.equals(timeScope) || MONTH.equals(timeScope)) {
                    String[] startTimeSplit = timeScopes[1].split("-");
                    String[] endTimeSplit = timeScopes[0].split("-");
                    String bgTime = startTimeSplit[0] + startTimeSplit[1] + startTimeSplit[2];
                    String endTime = endTimeSplit[0] + endTimeSplit[1] + endTimeSplit[2];
                    String sql = sqlKeyPersonnelToStationCount(bgTime, endTime);
                    keyPersonnelToStationCountDTOS = warningInformationRepository.findListBySql(KeyPersonnelToStationCountDTO.class, 0, sql, params);
                } else {
                    String[] startTimeSplit = timeScopes[1].split("-");
                    String[] endTimeSplit = timeScopes[0].split("-");
                    String bgTime = startTimeSplit[0] + startTimeSplit[1];
                    String endTime = endTimeSplit[0] + endTimeSplit[1];
                    String sqlYear = sqlKeyPersonnelToStationCountYear(bgTime, endTime);
                    keyPersonnelToStationCountDTOS = warningInformationRepository.findListBySql(KeyPersonnelToStationCountDTO.class, 0, sqlYear, params);
                }
            }
        }
        return keyPersonnelToStationCountDTOS;
    }

    /**
     * 重点人员到站统计Echarts数据 sql 查询语句（本周，本月）
     *
     * @param startTime
     * @param endTime
     * @return
     */
    private String sqlKeyPersonnelToStationCount(String startTime, String endTime) {
        String sql = "SELECT" +
                " w1.to_station_name as stationName," +
                " COUNT ( w1.* ) as stationNums " +
                "FROM" +
                " mpi_warning_information w1," +
                " ( SELECT arrival_date AS yeardate,id as formId FROM mpi_warning_information ) AS newdate " +
                "WHERE" +
                " ID IN ( SELECT warn_info_id FROM mpi_warn_info_user WHERE u_org_id in :uOrgIds ) " +
                " and newdate.formId = w1.id" +
                " AND newdate.yeardate >= '" + startTime + "' " +
                " AND newdate.yeardate <= '" + endTime + "' " +
                " AND w1.ticket_state = '1'  " +
                " AND w1.area != 1  " +
                "GROUP BY w1.to_station_name order by stationNums desc";
        return sql;
    }

    /**
     * 重点人员到站统计Echarts数据 sql 查询语句（半年，一年）
     *
     * @param startTime
     * @param endTime
     * @return
     */
    private String sqlKeyPersonnelToStationCountYear(String startTime, String endTime) {
        String sql =
                "SELECT w1.to_station_name as stationName," +
                        " COUNT ( w1.* ) as stationNums " +
                        "FROM" +
                        " mpi_warning_information w1," +
                        " ( SELECT SUBSTRING ( arrival_date FROM 1 FOR 6 ) AS yeardate,id as formId FROM mpi_warning_information ) AS newdate " +
                        "WHERE" +
                        " ID IN ( SELECT warn_info_id FROM mpi_warn_info_user WHERE u_org_id in :uOrgIds ) " +
                        " and newdate.formId = w1.id" +
                        " AND newdate.yeardate >= '" + startTime + "' " +
                        " AND newdate.yeardate <= '" + endTime + "' " +
                        " AND w1.ticket_state = '1'  " +
                        " AND w1.area != 1  " +
                        "GROUP BY w1.to_station_name order by stationNums desc";
        return sql;
    }

    /**
     * 各局核查物品及音频统计Echarts数据
     *
     * @param timeScope
     * @param uOrgId
     * @return
     */
    public List<BureausVerificationtemsAudioCountDTO> bureausVerificationtemsAudioCount(String timeScope, Long uOrgId, String trainDate, String arrivalDate) {
    	//通过组织机构id获取组织机构名称
    	String uOrgName = uOrgRepository.findOrgIdByName(uOrgId);
        Map<String, Object> params = new HashMap<>();
        List<BigInteger> bureausIds = uOrgRepository.findBureaus();
        String[] timeScopes = timeScope(timeScope);
        Long managerUorgId = 3L;
        
        /**
         * 判断当前进来的组织机构名称为 反恐处 / 反恐支队
         * 当进来的为反恐处 两种可能： 1.铁路总局-局机关-反恐处-人员    2. 18个公安局 局机关-反恐处-人员
         * 
         */
        if("反恐处".equals(uOrgName)) {
        	if (managerUorgId == uOrgId) {
        		return bureausVerificationtemsAudioCount(timeScope, trainDate, arrivalDate, params, bureausIds, timeScopes);
            }else {
            	//通过组织结构id查询 当前结构下的公安处id
            	bureausIds = uOrgRepository.findDeptIds(uOrgId);
            	return bureausVerificationtemsAudioCount(timeScope, trainDate, arrivalDate, params, bureausIds, timeScopes);
            }
        }

        if("反恐怖支队".equals(uOrgName)) {
        	//通过组织结构id查询 当前结构下的派出所/乘警 id
        	bureausIds = uOrgRepository.findPoliceIds(uOrgId);
        	return bureausVerificationtemsAudioCount(timeScope, trainDate, arrivalDate, params, bureausIds, timeScopes);
        }
        
        List<BureausVerificationtemsAudioCountDTO> bureausVerificationtemsAudioCountDTOS = new ArrayList<>();
        return bureausVerificationtemsAudioCountDTOS;


    }

	private List<BureausVerificationtemsAudioCountDTO> bureausVerificationtemsAudioCount(String timeScope, String trainDate, String arrivalDate,
			Map<String, Object> params, List<BigInteger> bureausIds, String[] timeScopes) {
		String sql;
		List<BureausVerificationtemsAudioCountDTO> bureausVerificationtemsAudioCountDTOS = new ArrayList<>();
		for (BigInteger bureausId : bureausIds) {
		    ArrayList<Long> bureausManageIds = EsConstants.powerList.get(Long.valueOf(bureausId.toString()));
		    List<String> roadBureauNames = uOrgRepository.findRoadBureauName(bureausManageIds);
            params.put("roadBureauNames", roadBureauNames);
		    if (!StringUtils.isEmpty(trainDate) && !StringUtils.isEmpty(arrivalDate)) {
		    	sql = "SELECT" +
                    " b3.NAME AS bureausName," +
                    " COUNT ( b1.* ) AS audioNums," +
                    " COUNT ( b2.* ) AS verificationtemsNums " +
                    "FROM" +
                    " ( SELECT NAME FROM u_org WHERE ID = " + bureausId + " ) AS b3," +
                    " (" +
                    " SELECT ID AS" +
                    " fid " +
                    " FROM" +
                    " ((" +
                    " SELECT SUBSTRING" +
                    " ( check_time FROM 1 FOR 10 ) AS yearDate," +
                    " ID AS checkid " +
                    " FROM" +
                    " mpi_check_record " +
                    " )) AS newdate," +
                    " mpi_check_record " +
                    " WHERE" +
                    " newdate.checkid = ID " +
                    " AND has_violent_video_yes = '是' " +
                    " AND newdate.yeardate >= '" + trainDate + "' " +
                    " AND newdate.yeardate <= '" + arrivalDate + "' " +
                    " AND check_unit IN :roadBureauNames) AS b1" +
                    " FULL JOIN (" +
                    " SELECT ID AS" +
                    " sid " +
                    " FROM" +
                    " ((" +
                    " SELECT SUBSTRING" +
                    " ( check_time FROM 1 FOR 10 ) AS yearDate," +
                    " ID AS checkid " +
                    " FROM" +
                    " mpi_check_record " +
                    " )) AS newdate," +
                    " mpi_check_record " +
                    " WHERE" +
                    " newdate.checkid = ID " +
                    " AND has_suspicious_item_yes = '是' " +
                    " AND newdate.yeardate >= '" + trainDate + "' " +
                    " AND newdate.yeardate <= '" + arrivalDate + "' " +
                    " AND check_unit IN :roadBureauNames " +
                    " ) AS b2 ON b1.fid = b2.sid " +
                    "GROUP BY" +
                    " bureausName";
        } else {
            if (timeScope.equals(WEEK) || timeScope.equals(MONTH)) {
            	sql = "SELECT" +
                        " b3.NAME AS bureausName," +
                        " COALESCE (COUNT(b1.* ), 0) AS audioNums," +
                        " COALESCE (COUNT( b2.* ),0) AS verificationtemsNums " +
                        "FROM" +
                        " ( SELECT NAME FROM u_org WHERE ID =" + bureausId + " ) AS b3," +
                        " (" +
                        " SELECT ID AS" +
                        " fid " +
                        " FROM" +
                        " ((" +
                        " SELECT SUBSTRING" +
                        " ( check_time FROM 1 FOR 10 ) AS yearDate," +
                        " ID AS checkid " +
                        " FROM" +
                        " mpi_check_record " +
                        " )) AS newdate," +
                        " mpi_check_record " +
                        " WHERE" +
                        " newdate.checkid = ID " +
                        " AND has_violent_video_yes = '是' " +
                        " AND newdate.yeardate >= '" + timeScopes[1] + "' " +
                        " AND newdate.yeardate <= '" + timeScopes[0] + "' " +
                        " AND check_unit IN :roadBureauNames) AS b1" +
                        " FULL JOIN (" +
                        " SELECT ID AS" +
                        " sid " +
                        " FROM" +
                        " ((" +
                        " SELECT SUBSTRING" +
                        " ( check_time FROM 1 FOR 10 ) AS yearDate," +
                        " ID AS checkid " +
                        " FROM" +
                        " mpi_check_record " +
                        " )) AS newdate," +
                        " mpi_check_record " +
                        " WHERE" +
                        " newdate.checkid = ID " +
                        " AND has_suspicious_item_yes = '是' " +
                        " AND newdate.yeardate >= '" + timeScopes[1] + "' " +
                        " AND newdate.yeardate <= '" + timeScopes[0] + "' " +
                        " AND check_unit IN :roadBureauNames " +
                        " ) AS b2 ON b1.fid = b2.sid " +
                        "GROUP BY" +
                        " bureausName";
            } else {
            	sql = "SELECT" +
                        " b3.NAME AS bureausName," +
                        " COUNT ( b1.* ) AS audioNums," +
                        " COUNT ( b2.* ) AS verificationtemsNums " +
                        " FROM" +
                        " ( SELECT NAME FROM u_org WHERE ID = " + bureausId + " ) AS b3," +
                        " (" +
                        " SELECT ID AS" +
                        " fid " +
                        " FROM" +
                        " ((" +
                        " SELECT SUBSTRING" +
                        " ( check_time FROM 1 FOR 7 ) AS yearDate," +
                        " ID AS checkid " +
                        " FROM" +
                        " mpi_check_record " +
                        " )) AS newdate," +
                        " mpi_check_record " +
                        " WHERE" +
                        " newdate.checkid = ID " +
                        " AND has_violent_video_yes = '是' " +
                        " AND newdate.yeardate >= '" + timeScopes[1] + "' " +
                        " AND newdate.yeardate <= '" + timeScopes[0] + "' " +
                        " AND check_unit IN :roadBureauNames) AS b1" +
                        " FULL JOIN (" +
                        " SELECT ID AS" +
                        " sid " +
                        " FROM" +
                        " ((" +
                        " SELECT SUBSTRING" +
                        " ( check_time FROM 1 FOR 7 ) AS yearDate," +
                        " ID AS checkid " +
                        " FROM" +
                        " mpi_check_record " +
                        " )) AS newdate," +
                        " mpi_check_record " +
                        " WHERE" +
                        " newdate.checkid = ID " +
                        " AND has_suspicious_item_yes = '是' " +
                        " AND newdate.yeardate >= '" + timeScopes[1] + "' " +
                        " AND newdate.yeardate <= '" + timeScopes[0] + "' " +
                        " AND check_unit IN :roadBureauNames " +
                        " ) AS b2 ON b1.fid = b2.sid " +
                        "GROUP BY" +
                        " bureausName";
            }
        }
		    List<BureausVerificationtemsAudioCountDTO> bureausVerificationtemsAudioCountDTOSS = warningInformationRepository.findListBySql(BureausVerificationtemsAudioCountDTO.class, 0, sql, params);
		    if (bureausVerificationtemsAudioCountDTOSS.size() > 0) {
		        bureausVerificationtemsAudioCountDTOS.addAll(bureausVerificationtemsAudioCountDTOSS);
		    }
		}
		return bureausVerificationtemsAudioCountDTOS;
	}

    /**
     * 各局核查人数统计Echarts数据
     *
     * @param timeScope
     * @param uOrgId
     * @return
     */
    public List<BureausCheckPersonnelCountDTO> bureausCheckPersonnelCount(String timeScope, Long uOrgId, String trainDate, String arrivalDate) {
    	//通过组织机构id获取组织机构名称
    	String uOrgName = uOrgRepository.findOrgIdByName(uOrgId);
        Map<String, Object> params = new HashMap<>();
        List<BigInteger> bureausIds = uOrgRepository.findBureaus();
        String[] timeScopes = timeScope(timeScope);
        Long managerUorgId = 3L;
        
        /**
         * 判断当前进来的组织机构名称为 反恐处 / 反恐支队
         * 当进来的为反恐处 两种可能： 1.铁路总局-局机关-反恐处-人员    2. 18个公安局 局机关-反恐处-人员
         * 
         */
        if("反恐处".equals(uOrgName)) {
        	if (managerUorgId == uOrgId) {
        		return checkPersonnelCount(timeScope, trainDate, arrivalDate, params, bureausIds, timeScopes);
            }else {
            	//通过组织结构id查询 当前结构下的公安处id
            	bureausIds = uOrgRepository.findDeptIds(uOrgId);
            	return checkPersonnelCount(timeScope, trainDate, arrivalDate, params, bureausIds, timeScopes);
            }
        }

        if("反恐怖支队".equals(uOrgName)) {
        	//通过组织结构id查询 当前结构下的派出所/乘警 id
        	bureausIds = uOrgRepository.findPoliceIds(uOrgId);
        	return checkPersonnelCount(timeScope, trainDate, arrivalDate, params, bureausIds, timeScopes);
        }
        
        List<BureausCheckPersonnelCountDTO> bureausCheckPersonnelCountDTOS = new ArrayList<>();
        return bureausCheckPersonnelCountDTOS;


    }

	private List<BureausCheckPersonnelCountDTO> checkPersonnelCount(String timeScope, String trainDate, String arrivalDate,
			Map<String, Object> params, List<BigInteger> bureausIds, String[] timeScopes) {
		String sql;
		List<BureausCheckPersonnelCountDTO> bureausCheckPersonnelCountDTOS = new ArrayList<>();
		for (BigInteger bureausId : bureausIds) {
		    ArrayList<Long> bureausManageIds = EsConstants.powerList.get(Long.valueOf(bureausId.toString()));
		    params.put("uOrgIds", bureausManageIds);
		    if (!StringUtils.isEmpty(trainDate) && !StringUtils.isEmpty(arrivalDate)) {
		        sql = "SELECT b3.NAME as bureausName,COUNT ( w1.* ) as verificationtemsNums " +
		                "FROM" +
		                " ( SELECT NAME FROM u_org WHERE ID = " + bureausId + " ) AS b3," +
		                " ( SELECT SUBSTRING ( create_time FROM 1 FOR 10 ) AS yearDate, ID AS checkid FROM mpi_warning_information ) AS newdate," +
		                " mpi_warning_information AS w1 " +
		                "WHERE" +
		                " check_state = '核查结束' " +
		                " AND newdate.yeardate >= '" + trainDate + "' " +
		                " AND newdate.yeardate <= '" + arrivalDate + "' " +
		                " AND newdate.checkid = w1.ID " +
		                " AND w1.ticket_state = '1'  " +
		                " AND w1.area != 1  " +
		                " AND ID IN (" +
		                "SELECT" +
		                " warn_info_id " +
		                "FROM" +
		                " mpi_warn_info_user " +
		                "WHERE" +
		                " u_org_id IN:uOrgIds) " +
		                "GROUP BY" +
		                " b3.NAME";
		    } else {
		        if (WEEK.equals(timeScope) || MONTH.equals(timeScope)) {
		            sql = "SELECT" +
		                    " b3.NAME as bureausName," +
		                    " COUNT ( w1.* ) as verificationtemsNums " +
		                    "FROM" +
		                    " ( SELECT NAME FROM u_org WHERE ID = " + bureausId + " ) AS b3," +
		                    " ( SELECT SUBSTRING ( create_time FROM 1 FOR 10 ) AS yearDate, ID AS checkid FROM mpi_warning_information ) AS newdate," +
		                    " mpi_warning_information AS w1 " +
		                    "WHERE" +
		                    " check_state = '核查结束' " +
		                    " AND newdate.yeardate >= '" + timeScopes[1] + "' " +
		                    " AND newdate.yeardate <= '" + timeScopes[0] + "' " +
		                    " AND newdate.checkid = w1.ID " +
		                    " AND w1.ticket_state = '1'  " +
		                    " AND w1.area != 1  " +
		                    " AND ID IN (" +
		                    "SELECT" +
		                    " warn_info_id " +
		                    "FROM" +
		                    " mpi_warn_info_user " +
		                    "WHERE" +
		                    " u_org_id IN:uOrgIds) " +
		                    "GROUP BY" +
		                    " b3.NAME";
		        } else {
		            sql = "SELECT" +
		                    " b3.NAME as bureausName," +
		                    " COUNT ( w1.* ) as verificationtemsNums " +
		                    "FROM" +
		                    " ( SELECT NAME FROM u_org WHERE ID = " + bureausId + " ) AS b3," +
		                    " ( SELECT SUBSTRING ( create_time FROM 1 FOR 7 ) AS yearDate, ID AS checkid FROM mpi_warning_information ) AS newdate," +
		                    " mpi_warning_information AS w1 " +
		                    "WHERE" +
		                    " check_state = '核查结束' " +
		                    " AND newdate.yeardate >= '" + timeScopes[1] + "' " +
		                    " AND newdate.yeardate <= '" + timeScopes[0] + "' " +
		                    " AND newdate.checkid = w1.ID " +
		                    " AND w1.ticket_state = '1'  " +
		                    " AND w1.area != 1  " +
		                    " AND ID IN (" +
		                    "SELECT" +
		                    " warn_info_id " +
		                    "FROM" +
		                    " mpi_warn_info_user " +
		                    "WHERE" +
		                    " u_org_id IN:uOrgIds) " +
		                    "GROUP BY" +
		                    " b3.NAME";
		        }

		    }
		    List<BureausCheckPersonnelCountDTO> bureausCheckPersonnelCountDTOSS = warningInformationRepository.findListBySql(BureausCheckPersonnelCountDTO.class, 0, sql, params);
		    if (bureausCheckPersonnelCountDTOSS.size() > 0) {
		        bureausCheckPersonnelCountDTOS.addAll(bureausCheckPersonnelCountDTOSS);
		    }
		}
		return bureausCheckPersonnelCountDTOS;
	}

    /**
     * 重点人员流入流出统计Echart数据
     *
     * @param deptNames
     * @param uOrgId
     * @return
     */
    public List<KeyPersonnelInAndOutFlowCountDTO> keyPersonnelInAndOutFlowCount(String startDate, String endDate, List<String> deptNames, Long uOrgId, String origin, String dataSource) {

        List<KeyPersonnelInAndOutFlowCountDTO> keyPersonnelInAndOutFlowCountDTOS = null;
        String[] timeScopes = timeScope(MONTH);
        String timeScopesStartDate = timeScopes[1];
        String timeScopesEndDate = timeScopes[0];
        String[] startTimeSplit = timeScopesStartDate.split("-");
        String startTime = startTimeSplit[0] + startTimeSplit[1] + startTimeSplit[2];
        String[] endTimeSplit = timeScopesEndDate.split("-");
        String endTime = endTimeSplit[0] + endTimeSplit[1] + endTimeSplit[2];
        boolean originState = true;
        if (StringUtils.isEmpty(origin) || "请选择".equals(origin)) {
            originState = false;
        }
        ArrayList<Integer> areas = new ArrayList<>();
        if (!StringUtils.isEmpty(dataSource) && dataSource.equals("1")) {
            areas.add(1);
        } else if (!StringUtils.isEmpty(dataSource) && dataSource.equals("2")) {
            areas.add(2);
            areas.add(3);
            areas.add(4);
        } else {
            areas.add(1);
            areas.add(2);
            areas.add(3);
            areas.add(4);
        }
        if (!originState) {
            Map<String, Object> params = new HashMap<>();
            params.put("areas", areas);
            UOrg one = uOrgRepository.findOne(uOrgId);
            if ("800000000000".equalsIgnoreCase(one.getmOrgCode())) {
                List<String> stationNames = stationManageRepository.findAllStationName();
                List<String> newDeptNames = new ArrayList<>();
                stationNamesConvert(stationNames, newDeptNames);
                params.put("newDeptNames", newDeptNames);
            } else {
                List<String> stationNames = stationManageRepository.findStationName(one.getmOrgCode());
                List<String> newDeptNames = new ArrayList<>();
                stationNamesConvert(stationNames, newDeptNames);
                params.put("newDeptNames", newDeptNames);
            }
            if (StringUtils.isEmpty(startDate) && StringUtils.isEmpty(endDate) && StringUtils.isEmpty(deptNames)) {
                String sql = "select COALESCE (tmp2.count, 0 ) as inCount , COALESCE(tmp1.count,0) as outCount , timeScope.selectTime as xAsix from " +
                        "(SELECT to_char( DATE ( zz ), 'yyyymmdd' ) AS selectTime FROM generate_series (date_trunc( 'day', to_date( '" + startTime + "', 'yyyymmdd' ))," +
                        "date_trunc( 'day', to_date( '" + endTime + "', 'yyyymmdd' )), '1 day') AS tt ( zz ))AS timeScope left join" +
                        "(SELECT count(*),train_date FROM mpi_warning_information wi WHERE from_station_name in:newDeptNames AND wi.ticket_state = '1' and wi.area in :areas  GROUP BY train_date) as tmp1 on tmp1.train_date = timeScope.selectTime" +
                        " left join " +
                        "(SELECT count(*),arrival_date FROM mpi_warning_information wi WHERE to_station_name in:newDeptNames AND wi.ticket_state = '1' and wi.area in :areas  GROUP BY arrival_date" +
                        ") as tmp2 on tmp2.arrival_date = timeScope.selectTime";

                keyPersonnelInAndOutFlowCountDTOS = warningInformationRepository.findListBySql(KeyPersonnelInAndOutFlowCountDTO.class, 0, sql, params);
            } else {
                if (StringUtils.isEmpty(deptNames) && !StringUtils.isEmpty(startDate) && !StringUtils.isEmpty(endDate)) {
                    String[] StartDate = startDate.split("-");
                    String[] EndDate = endDate.split("-");
                    String startTimeSelect = StartDate[0] + StartDate[1] + StartDate[2];
                    String endTimeSelect = EndDate[0] + EndDate[1] + EndDate[2];
                    String sql = "select COALESCE (tmp2.count, 0 ) as inCount , COALESCE(tmp1.count,0) as outCount , timeScope.selectTime as xAsix from " +
                            "(SELECT to_char( DATE ( zz ), 'yyyymmdd' ) AS selectTime FROM generate_series (date_trunc( 'day', to_date( '" + startTimeSelect + "', 'yyyymmdd' ))," +
                            "date_trunc( 'day', to_date( '" + endTimeSelect + "', 'yyyymmdd' )), '1 day') AS tt ( zz ))AS timeScope left join" +
                            "(SELECT count(*),train_date FROM mpi_warning_information wi WHERE from_station_name in:newDeptNames AND wi.ticket_state = '1' and wi.area in :areas  GROUP BY train_date) as tmp1 on tmp1.train_date = timeScope.selectTime" +
                            " left join " +
                            "(SELECT count(*),arrival_date FROM mpi_warning_information wi WHERE to_station_name in:newDeptNames AND wi.ticket_state = '1' and wi.area in :areas  GROUP BY arrival_date" +
                            ") as tmp2 on tmp2.arrival_date = timeScope.selectTime";
                    keyPersonnelInAndOutFlowCountDTOS = warningInformationRepository.findListBySql(KeyPersonnelInAndOutFlowCountDTO.class, 0, sql, params);
                }
                if (!StringUtils.isEmpty(deptNames) && StringUtils.isEmpty(startDate) && StringUtils.isEmpty(endDate)) {
                    String s = deptNames.get(0);
                    keyPersonnelInAndOutFlowCountDTOS = getPrivateKeyPersonnelInAndOutFlowCountDTOS(deptNames, params, startTime, endTime, s, originState, origin, areas);
                }
                keyPersonnelInAndOutFlowCountDTOS = getInnerKeyPersonnelInAndOutFlowCountDTOS(startDate, endDate, deptNames, origin, params, keyPersonnelInAndOutFlowCountDTOS, originState, areas);
            }
        } else {
            Map<String, Object> params = new HashMap<>();
            params.put("areas", areas);
            UOrg one = uOrgRepository.findOne(uOrgId);
            if ("800000000000".equalsIgnoreCase(one.getmOrgCode())) {
                List<String> stationNames = stationManageRepository.findAllStationName();
                List<String> newDeptNames = new ArrayList<>();
                stationNamesConvert(stationNames, newDeptNames);
                params.put("newDeptNames", newDeptNames);
            } else {
                List<String> stationNames = stationManageRepository.findStationName(one.getmOrgCode());
                List<String> newDeptNames = new ArrayList<>();
                stationNamesConvert(stationNames, newDeptNames);
                params.put("newDeptNames", newDeptNames);
            }
            if (StringUtils.isEmpty(startDate) && StringUtils.isEmpty(endDate) && StringUtils.isEmpty(deptNames)) {
                String sql = "select COALESCE (tmp2.count, 0 ) as inCount , COALESCE(tmp1.count,0) as outCount , timeScope.selectTime as xAsix from " +
                        "(SELECT to_char( DATE ( zz ), 'yyyymmdd' ) AS selectTime FROM generate_series (date_trunc( 'day', to_date( '" + startTime + "', 'yyyymmdd' ))," +
                        "date_trunc( 'day', to_date( '" + endTime + "', 'yyyymmdd' )), '1 day') AS tt ( zz ))AS timeScope left join" +
                        "(SELECT count(*),train_date FROM mpi_warning_information wi WHERE from_station_name in:newDeptNames  AND wi.ticket_state = '1'  and wi.area in :areas  and nation = '" + origin + "' GROUP BY train_date) as tmp1 on tmp1.train_date = timeScope.selectTime" +
                        " left join " +
                        "(SELECT count(*),arrival_date FROM mpi_warning_information wi WHERE to_station_name in:newDeptNames  AND wi.ticket_state = '1' and wi.area in :areas  and nation = '" + origin + "'GROUP BY arrival_date" +
                        ") as tmp2 on tmp2.arrival_date = ti" +
                        "meScope.selectTime";
                keyPersonnelInAndOutFlowCountDTOS = warningInformationRepository.findListBySql(KeyPersonnelInAndOutFlowCountDTO.class, 0, sql, params);
            } else {
                if (StringUtils.isEmpty(deptNames) && !StringUtils.isEmpty(startDate) && !StringUtils.isEmpty(endDate)) {
                    String[] StartDate = startDate.split("-");
                    String[] EndDate = endDate.split("-");
                    String startTimeSelect = StartDate[0] + StartDate[1] + StartDate[2];
                    String endTimeSelect = EndDate[0] + EndDate[1] + EndDate[2];
                    String sql = "select COALESCE (tmp2.count, 0 ) as inCount , COALESCE(tmp1.count,0) as outCount , timeScope.selectTime as xAsix from " +
                            "(SELECT to_char( DATE ( zz ), 'yyyymmdd' ) AS selectTime FROM generate_series (date_trunc( 'day', to_date( '" + startTimeSelect + "', 'yyyymmdd' ))," +
                            "date_trunc( 'day', to_date( '" + endTimeSelect + "', 'yyyymmdd' )), '1 day') AS tt ( zz ))AS timeScope left join" +
                            "(SELECT count(*),train_date FROM mpi_warning_information wi WHERE from_station_name in:newDeptNames  AND wi.ticket_state = '1'  and wi.area in :areas  and nation = '" + origin + "'GROUP BY train_date) as tmp1 on tmp1.train_date = timeScope.selectTime" +
                            " left join " +
                            "(SELECT count(*),arrival_date FROM mpi_warning_information wi WHERE to_station_name in:newDeptNames  AND wi.ticket_state = '1'  and wi.area in :areas  and nation = '" + origin + "' GROUP BY arrival_date) as tmp2 on tmp2.arrival_date = timeScope.selectTime";
                    keyPersonnelInAndOutFlowCountDTOS = warningInformationRepository.findListBySql(KeyPersonnelInAndOutFlowCountDTO.class, 0, sql, params);
                }
                if (!StringUtils.isEmpty(deptNames) && StringUtils.isEmpty(startDate) && StringUtils.isEmpty(endDate)) {
                    String s = deptNames.get(0);
                    keyPersonnelInAndOutFlowCountDTOS = getPrivateKeyPersonnelInAndOutFlowCountDTOS(deptNames, params, startTime, endTime, s, originState, origin, areas);
                }
                keyPersonnelInAndOutFlowCountDTOS = getInnerKeyPersonnelInAndOutFlowCountDTOS(startDate, endDate, deptNames, origin, params, keyPersonnelInAndOutFlowCountDTOS, originState, areas);
            }
        }


        return keyPersonnelInAndOutFlowCountDTOS;
    }

    private void stationNamesConvert(List<String> stationNames, List<String> newDeptNames) {
        for (String deptName : stationNames
                ) {
            if (deptName.contains("火车站")) {
                deptName = deptName.replace("火车站", "");
                newDeptNames.add(deptName);
            } else if (deptName.contains("车站")) {
                deptName = deptName.replace("车站", "");
                newDeptNames.add(deptName);
            } else if (deptName.contains("站")) {
                deptName = deptName.replace("站", "");
                newDeptNames.add(deptName);
            } else {
                newDeptNames.add(deptName);
            }
        }
    }

    private List<KeyPersonnelInAndOutFlowCountDTO> getInnerKeyPersonnelInAndOutFlowCountDTOS(String startDate, String endDate, List<String> deptNames, String origin, Map<String, Object> params, List<KeyPersonnelInAndOutFlowCountDTO> keyPersonnelInAndOutFlowCountDTOS, boolean originState, ArrayList areas) {
        if (!StringUtils.isEmpty(deptNames) && !StringUtils.isEmpty(startDate) && !StringUtils.isEmpty(endDate)) {
            String[] StartDate = startDate.split("-");
            String[] EndDate = endDate.split("-");
            String startTimeSelect = StartDate[0] + StartDate[1] + StartDate[2];
            String endTimeSelect = EndDate[0] + EndDate[1] + EndDate[2];
            String s = deptNames.get(0);
            keyPersonnelInAndOutFlowCountDTOS = getPrivateKeyPersonnelInAndOutFlowCountDTOS(deptNames, params, startTimeSelect, endTimeSelect, s, originState, origin, areas);
        }
        return keyPersonnelInAndOutFlowCountDTOS;
    }

    private List<KeyPersonnelInAndOutFlowCountDTO> getPrivateKeyPersonnelInAndOutFlowCountDTOS(List<String> deptNames, Map<String, Object> params, String startTimeSelect, String endTimeSelect, String s, boolean originState, String origin, ArrayList areas) {
        List<KeyPersonnelInAndOutFlowCountDTO> keyPersonnelInAndOutFlowCountDTOS;
        List<String> stationNames;
        if (s.contains("公安局") || s.contains("公安处") || s.contains("派出所")) {
            if (s.contains("公安局")) {
                stationNames = stationManageRepository.findPassengerStationByBureauNameIn(deptNames);
            } else if (s.contains("公安处")) {
                stationNames = stationManageRepository.findPassengerStationByDeptNameIn(deptNames);
            } else {
                stationNames = stationManageRepository.findPassengerStationByPoliceStationIn(deptNames);
            }
            List<String> newDeptNames = new ArrayList<>();
            stationNamesConvert(stationNames, newDeptNames);
            params.put("newDeptNames", newDeptNames);
            params.put("areas", areas);
            String sql;
            if (!originState) {
                sql = "select COALESCE (tmp2.count, 0 ) as inCount , COALESCE(tmp1.count,0) as outCount , timeScope.selectTime as xAsix from " +
                        "(SELECT to_char( DATE ( zz ), 'yyyymmdd' ) AS selectTime FROM generate_series (date_trunc( 'day', to_date( '" + startTimeSelect + "', 'yyyymmdd' ))," +
                        "date_trunc( 'day', to_date( '" + endTimeSelect + "', 'yyyymmdd' )), '1 day') AS tt ( zz ))AS timeScope left join" +
                        "(SELECT count(*),train_date FROM mpi_warning_information wi WHERE from_station_name in:newDeptNames AND wi.ticket_state = '1' and wi.area in :areas  GROUP BY train_date) as tmp1 on tmp1.train_date = timeScope.selectTime" +
                        " left join " +
                        "(SELECT count(*),arrival_date FROM mpi_warning_information wi WHERE to_station_name in:newDeptNames AND wi.ticket_state = '1' and wi.area in :areas  GROUP BY arrival_date" +
                        ") as tmp2 on tmp2.arrival_date = timeScope.selectTime";
            } else {
                sql = "select COALESCE (tmp2.count, 0 ) as inCount , COALESCE(tmp1.count,0) as outCount , timeScope.selectTime as xAsix from " +
                        "(SELECT to_char( DATE ( zz ), 'yyyymmdd' ) AS selectTime FROM generate_series (date_trunc( 'day', to_date( '" + startTimeSelect + "', 'yyyymmdd' ))," +
                        "date_trunc( 'day', to_date( '" + endTimeSelect + "', 'yyyymmdd' )), '1 day') AS tt ( zz ))AS timeScope left join" +
                        "(SELECT count(*),train_date FROM mpi_warning_information wi WHERE from_station_name in:newDeptNames AND wi.ticket_state = '1' and wi.area in :areas  and nation = '" + origin + "' GROUP BY train_date) as tmp1 on tmp1.train_date = timeScope.selectTime" +
                        " left join " +
                        "(SELECT count(*),arrival_date FROM mpi_warning_information wi WHERE to_station_name in:newDeptNames AND wi.ticket_state = '1'  and wi.area in :areas and nation = '" + origin + "' GROUP BY arrival_date" +
                        ") as tmp2 on tmp2.arrival_date = timeScope.selectTime";
            }
            keyPersonnelInAndOutFlowCountDTOS = warningInformationRepository.findListBySql(KeyPersonnelInAndOutFlowCountDTO.class, 0, sql, params);
        } else {
            Map<String, Object> paramsSelect = new HashMap<>();
            String sql;
            List<String> deptNameSelect = new ArrayList<>();
            stationNamesConvert(deptNames, deptNameSelect);
            paramsSelect.put("depts", deptNameSelect);
            paramsSelect.put("areas", areas);
            if (!originState) {
                sql = "SELECT COALESCE (tmp1.COUNT, 0) AS inCount,COALESCE (tmp2.COUNT, 0) AS outCount,timeScope.selectTime AS xAsix FROM " +
                        " (SELECT to_char( DATE ( zz ), 'yyyymmdd' ) AS selectTime FROM generate_series ( date_trunc( 'day', to_date( '" + startTimeSelect + "', 'yyyymmdd' )), date_trunc( 'day', to_date( '" + endTimeSelect + "', 'yyyymmdd' )), '1 day' ) AS tt ( zz )) AS timeScope " +
                        " LEFT JOIN ( " +
                        "SELECT COUNT " +
                        " ( * ), " +
                        " train_date  " +
                        "FROM " +
                        " mpi_warning_information  " +
                        "WHERE " +
                        " from_station_name IN:depts  " +
                        " AND ticket_state = '1' " +
                        " AND area in :areas " +
                        "GROUP BY " +
                        " train_date  " +
                        " ) tmp2 ON tmp2.train_date = timeScope.selectTime " +
                        " LEFT JOIN ( " +
                        "SELECT COUNT " +
                        " ( * ), " +
                        " arrival_date  " +
                        "FROM " +
                        " mpi_warning_information  " +
                        "WHERE " +
                        " to_station_name IN:depts " +
                        " AND ticket_state = '1' " +
                        " AND area in :areas " +
                        "GROUP BY " +
                        " arrival_date  " +
                        " ) tmp1 ON tmp1.arrival_date = timeScope.selectTime";
            } else {
                sql = "SELECT COALESCE (tmp1.COUNT, 0) AS inCount,COALESCE (tmp2.COUNT, 0) AS outCount,timeScope.selectTime AS xAsix FROM " +
                        " (SELECT to_char( DATE ( zz ), 'yyyymmdd' ) AS selectTime FROM generate_series ( date_trunc( 'day', to_date( '" + startTimeSelect + "', 'yyyymmdd' )), date_trunc( 'day', to_date( '" + endTimeSelect + "', 'yyyymmdd' )), '1 day' ) AS tt ( zz )) AS timeScope " +
                        " LEFT JOIN ( " +
                        "SELECT COUNT " +
                        " ( * ), " +
                        " train_date  " +
                        "FROM " +
                        " mpi_warning_information  " +
                        "WHERE " +
                        " from_station_name IN:depts and nation = '" + origin + "'  " +
                        " AND ticket_state = '1' " +
                        " AND area in :areas " +
                        "GROUP BY " +
                        " train_date  " +
                        " ) tmp2 ON tmp2.train_date = timeScope.selectTime " +
                        " LEFT JOIN ( " +
                        "SELECT COUNT " +
                        " ( * ), " +
                        " arrival_date  " +
                        "FROM " +
                        " mpi_warning_information  " +
                        "WHERE " +
                        " to_station_name IN:depts and nation = '" + origin + "'  " +
                        " AND ticket_state = '1' " +
                        " AND area in :areas " +
                        "GROUP BY " +
                        " arrival_date  " +
                        " ) tmp1 ON tmp1.arrival_date = timeScope.selectTime";
            }
            keyPersonnelInAndOutFlowCountDTOS = warningInformationRepository.findListBySql(KeyPersonnelInAndOutFlowCountDTO.class, 0, sql, paramsSelect);
        }
        return keyPersonnelInAndOutFlowCountDTOS;
    }

    /**
     * 重点人员流入流出统计Echarts,组织下拉框填充One
     *
     * @param uOrgId
     * @return
     */
    public List<KeyPersonnelInAndOutFlowSelectDTO> keyPersonnelInAndOutFlowCountSelectOne(Long uOrgId) {
        ArrayList<Long> uOrgIds = EsConstants.powerList.get(uOrgId);
        Map<String, Object> params = new HashMap<>();
        params.put("uOrgIds", uOrgIds);
        String fSql = "SELECT distinct bureau_name as dataName,bureau_id as dataId FROM mpi_station_manage sm WHERE sm.police_station_id in(SELECT org.code FROM u_org org WHERE org.id in :uOrgIds)";
        List<KeyPersonnelInAndOutFlowSelectDTO> keyPersonnelInAndOutFlowSelectDTOS = warningInformationRepository.findListBySql(KeyPersonnelInAndOutFlowSelectDTO.class, 0, fSql, params);
        return keyPersonnelInAndOutFlowSelectDTOS;
    }

    /**
     * 重点人员流入流出统计Echarts,组织下拉框填充Two
     *
     * @param deptNames
     * @return
     */
    public List<KeyPersonnelInAndOutFlowSelectTwoDTO> keyPersonnelInAndOutFlowCountSelectTwo(List<String> deptNames, Long uOrgId) {
        Map<String, Object> params = new HashMap<>();
        params.put("deptNames", deptNames);
        ArrayList<Long> uOrgIds = EsConstants.powerList.get(uOrgId);
        params.put("uOrgIds", uOrgIds);
        String Sql = "select distinct dept_name as dataName,dept_id as dataId from mpi_station_manage where bureau_name in:deptNames and police_station_id in(SELECT org.code FROM u_org org WHERE org.id in :uOrgIds)";
        List<KeyPersonnelInAndOutFlowSelectTwoDTO> keyPersonnelInAndOutFlowSelectDTOS = warningInformationRepository.findListBySql(KeyPersonnelInAndOutFlowSelectTwoDTO.class, 0, Sql, params);
        return keyPersonnelInAndOutFlowSelectDTOS;
    }

    /**
     * 重点人员流入流出统计Echarts,组织下拉框填充Three
     *
     * @param deptNames
     * @return
     */
    public List<KeyPersonnelInAndOutFlowSelectThreeDTO> keyPersonnelInAndOutFlowCountSelectThree(List<String> deptNames) {
        Map<String, Object> params = new HashMap<>();
        params.put("deptNames", deptNames);
        String fSql = "select distinct police_station as dataName,police_station_id as dataId from mpi_station_manage where dept_name in:deptNames";
        List<KeyPersonnelInAndOutFlowSelectThreeDTO> keyPersonnelInAndOutFlowSelectDTOS = warningInformationRepository.findListBySql(KeyPersonnelInAndOutFlowSelectThreeDTO.class, 0, fSql, params);
        return keyPersonnelInAndOutFlowSelectDTOS;
    }

    /**
     * 重点人员流入流出统计Echarts,组织下拉框填充Four
     *
     * @param deptNames
     * @return
     */
    public List<KeyPersonnelInAndOutFlowSelectFourDTO> keyPersonnelInAndOutFlowCountSelectFour(List<String> deptNames) {
        Map<String, Object> params = new HashMap<>();
        String s = deptNames.get(0);
        params.put("deptNames", deptNames);
        String fSql;
        if (s.contains("派出所")) {
            fSql = "SELECT passenger_station AS dataName,dept_id as dataId FROM mpi_station_manage WHERE police_station IN:deptNames";
        } else {
            fSql = "SELECT passenger_station AS dataName,dept_id as dataId FROM mpi_station_manage WHERE dept_name IN:deptNames";
        }
        List<KeyPersonnelInAndOutFlowSelectFourDTO> KeyPersonnelInAndOutFlowSelectFourDTOS = warningInformationRepository.findListBySql(KeyPersonnelInAndOutFlowSelectFourDTO.class, 0, fSql, params);
        return KeyPersonnelInAndOutFlowSelectFourDTOS;
    }
}
